# _*_ coding:utf-8 _*_
# at:2023-11-29
# author:zizle
import datetime
import json
import pathlib
import shutil
import threading
from enum import Enum
from fastapi import APIRouter, Query, Header, Body, Form, UploadFile, BackgroundTasks
from pydantic import BaseModel
from db import FAConnection
from hutool import security
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import char_utils, datetime_utils, file_utils, doc_covert
from v1_all_api.constant import COORDINATION_STATES, LECTURE_TYPES, LECTURE_SCALE_TYPES, EXCHANGE_LIB_NAME
from configs import STATIC_FILE_HOST


class CoordinationType(Enum):
    ALL = 'ALL'
    LECTURE = 'Lecture'  # 演讲培训
    ACCOMPANY = 'Accompany'  # 陪同走访
    INVESTMENT_PLAN = 'InvestmentPlan'  # 投资计划
    HEDGING = 'Hedging'  # 套保方案
    CUSTOMER_ONLINE = 'CustomerOnline'  # 线上行情交流
    # ANALYSIS_REPORT = 'AnalysisReport'  # 分析报告
    # PPT_COURSEWARE = 'PptCourseware'  # ppt课件
    WX_GROUP = 'WxGroup'  # 微信沟通平台
    RUIZHI = 'Ruizhi'  # 瑞智系统
    SHOU_QUAN = 'ShouQuan'  # 产品授权
    DATA_NEED = 'DataNeed'  # 其他
    # WIND_REMOTE = 'WindRemote'  # wind远程
    # ZHENDUAN = 'ZhenDuan'  # 交易诊断

    @classmethod
    def get_name(cls, val) -> str:
        return {
            'Lecture': '演讲培训',
            'Accompany': '陪同走访',
            'InvestmentPlan': '投资计划',
            'Hedging': '套保方案',
            'CustomerOnline': '线上行情交流',
            # 'AnalysisReport': '分析报告',
            # 'PptCourseware': 'PPT课件',
            'WxGroup': '微信沟通平台',
            # 'WindRemote': 'wind远程',
            'Ruizhi': '瑞智系统试用',
            # 'ZhenDuan': '期易通交易诊断'
            'ShouQuan': '产品授权',
            'DataNeed': '其他',
        }.get(val.value, '')

    @classmethod
    def get_icon(cls, val) -> str:
        return {
            'Lecture': 'user-ninja',
            'Accompany': 'coffee',
            'InvestmentPlan': 'book',
            'Hedging': 'file-contract',
            'CustomerOnline': 'paper-plane',
            'AnalysisReport': 'file-signature',
            'PptCourseware': 'file-powerpoint',
            'DataNeed': 'shopping-bag',
            'WxGroup': 'comments',
            'WindRemote': 'desktop',
            'Ruizhi': 'burn',
            'ZhenDuan': 'print',
            'ShouQuan': 'user-check'
        }.get(val.value, '')

    @classmethod
    def get_quota(cls, val) -> int:
        return {
            'Lecture': 10000,
            'Accompany': 8000,
            'InvestmentPlan': 5000,
            'Hedging': 6000,
            'CustomerOnline': 5000,
            'AnalysisReport': 0,
            'PptCourseware': 0,
            'DataNeed': 5000,
            'WxGroup': 20000,
            'WindRemote': 0,
            'Ruizhi': 5000,
            'ZhenDuan': 0,
            'ShouQuan': 6000,
        }.get(val.value, '')


class DispatchItem(BaseModel):
    record_id: int
    worker_id: int
    state: int
    explain_state: str


class CoordinationUtils:
    def __init__(self, coordination: CoordinationType):
        self.coordination = coordination

    def quota_change_log(self, user_id: int, prse_id: int, cursor=None, change: int = 0):
        cursor.execute('SELECT quota_remain FROM ruizy_user_quota WHERE user_id=%s ORDER BY create_time DESC LIMIT 1;',
                       [user_id])
        remain_ = cursor.fetchone()
        if not remain_:
            remain_quota = 0
        else:
            remain_quota = remain_['quota_remain']
        now_remain = remain_quota - change
        sql = """
            INSERT INTO ruizy_user_quota (user_id,module_name,module_id,description,quota_change,quota_remain)
            VALUES (%s,%s,%s,%s,%s,%s);
        """
        if change >= 0:
            desc = '申请【{}】消耗【{}】'.format(CoordinationType.get_name(self.coordination), change)
        else:
            desc = '退回【{}】增加【{}】'.format(CoordinationType.get_name(self.coordination), -change)
        cursor.execute(sql, [user_id, self.coordination.value, prse_id, desc, change, now_remain])

    def covert_file2image(self, annex_file: pathlib.Path):
        preview_folder = annex_file.parent.joinpath(annex_file.name + 'img')
        # 不存在，就得转化
        if not preview_folder.exists():
            preview_folder.mkdir(parents=True)
        if annex_file.name.endswith('.doc') or annex_file.name.endswith('.docx'):
            # 转为pdf
            pdf_filename = annex_file.name.replace('.docx', '.pdf').replace('.doc', '.pdf')
            pdf_file = annex_file.parent.joinpath(pdf_filename)
            pdf_file_tmp = doc_covert.docx2pdf(str(annex_file), pdf_file)
            # 转为images
            images = doc_covert.pdf2image(pdf_file_tmp, preview_folder)
            if pdf_file.exists() and pdf_file.is_file():
                pdf_file.unlink()
        elif annex_file.name.endswith('.ppt') or annex_file.name.endswith('.pptx'):
            if preview_folder.exists():
                shutil.rmtree(preview_folder, ignore_errors=True)
            images = doc_covert.ppt2pdf(annex_file, preview_folder, is_image=True)
        elif annex_file.name.endswith('.pdf'):
            images = doc_covert.pdf2image(annex_file, preview_folder)
        else:
            return []
        return images


class CoordinationRowHandler:

    def accompany_handler(self, rows):
        coordination_name = CoordinationType.get_name(CoordinationType.ACCOMPANY)
        coordination_key = CoordinationType.ACCOMPANY.value
        for item in rows:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['visit_to']
            item['coordinate_time'] = datetime_utils.to_string(item['visit_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['visit_time'] = str(item['visit_time'])
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')

        columns = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'visit_to', 'label': '走访对象'},
            {'prop': 'visit_site', 'label': '走访地点'},
            {'prop': 'visit_time', 'label': '走访时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return rows, columns

    def customer_online_handler(self, rows):
        coordination_name = CoordinationType.get_name(CoordinationType.CUSTOMER_ONLINE)
        coordination_key = CoordinationType.CUSTOMER_ONLINE.value
        for item in rows:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['company_name'] + '线上服务'
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = str(item['finish_time'])
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        columns = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'company_name', 'label': '交流对象'},
            {'prop': 'variety', 'label': '交流品种'},
            {'prop': 'finish_time', 'label': '交流时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return rows, columns

    def investment_plan_handler(self, rows):
        coordination_name = CoordinationType.get_name(CoordinationType.INVESTMENT_PLAN)
        coordination_key = CoordinationType.INVESTMENT_PLAN.value
        for item in rows:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['variety'] + '投资计划'
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = str(item['finish_time'])
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        columns = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'variety', 'label': '投资品种'},
            {'prop': 'fund', 'label': '投资资金'},
            {'prop': 'cycle', 'label': '投资周期', 'width': 100},
            {'prop': 'finish_time', 'label': '完成时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return rows, columns

    def lecture_handler(self, rows):
        coordination_name = CoordinationType.get_name(CoordinationType.LECTURE)
        coordination_key = CoordinationType.LECTURE.value
        for item in rows:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['topic']
            item['coordinate_time'] = datetime_utils.to_string(item['lecture_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['lecture_time'] = str(item['lecture_time'])
            item['scale_plain'] = LECTURE_SCALE_TYPES.get(item['scale'], '未知')
            item['line_plain'] = LECTURE_TYPES.get(item['line'], '未知')
            item['line_scale'] = item['line_plain'][:2] + item['scale_plain'][2:]
            item['exchange_plain'] = EXCHANGE_LIB_NAME.get(item['exchange'], '无')
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']

        columns = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'topic', 'label': '主题'},
            {'prop': 'target', 'label': '讲座对象'},
            {'prop': 'exchange_plain', 'label': '交易所', 'width': 100},
            {'prop': 'lecture_time', 'label': '讲课时间', 'width': 160},
            {'prop': 'line_scale', 'label': '类型规模', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return rows, columns

    def hedging_handler(self, rows):
        coordination_name = CoordinationType.get_name(CoordinationType.HEDGING)
        coordination_key = CoordinationType.HEDGING.value
        for item in rows:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['company_name'] + item['variety'] + '套保方案'
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = str(item['finish_time'])
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        columns = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'company_name', 'label': '公司名称'},
            {'prop': 'company_type', 'label': '公司类型'},
            {'prop': 'variety', 'label': '涉及品种'},
            {'prop': 'cycle', 'label': '投资周期'},
            {'prop': 'hedge_scale', 'label': '套保规模'},
            {'prop': 'finish_time', 'label': '完成时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return rows, columns

    def analysis_report_handler(self, rows):
        coordination_name = CoordinationType.get_name(CoordinationType.ANALYSIS_REPORT)
        coordination_key = CoordinationType.ANALYSIS_REPORT.value
        for item in rows:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['title']
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = str(item['finish_time'])
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        columns = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'title', 'label': '主题'},
            {'prop': 'company_name', 'label': '报告对象'},
            {'prop': 'finish_time', 'label': '完成时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return rows, columns

    def shou_quan_handler(self, rows):
        coordination_name = CoordinationType.get_name(CoordinationType.SHOU_QUAN)
        coordination_key = CoordinationType.SHOU_QUAN.value
        for item in rows:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = '授权' + item['apply_msg']
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = item['coordinate_time']
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex_file']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex_file']
                del item['annex_file']
        columns = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'finish_time', 'label': '服务时间', 'width': 160},
            {'prop': 'apply_msg', 'label': '申请内容'},
            {'prop': 'apply_plat', 'label': '申请平台'},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return rows, columns

    def data_need_handler(self, rows):
        coordination_name = CoordinationType.get_name(CoordinationType.DATA_NEED)
        coordination_key = CoordinationType.DATA_NEED.value
        for item in rows:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['title']
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = str(item['finish_time'])
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        columns = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'title', 'label': '材料主题'},
            {'prop': 'data_type', 'label': '材料类型'},
            {'prop': 'use_to', 'label': '材料用途'},
            {'prop': 'finish_time', 'label': '完成时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return rows, columns

    def wx_group_handler(self, rows):
        coordination_name = CoordinationType.get_name(CoordinationType.WX_GROUP)
        coordination_key = CoordinationType.WX_GROUP.value
        for item in rows:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['wx_name']
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = str(item['finish_time'])
            item['expire_time'] = datetime_utils.to_string(item['expire_time'], is_time=False) if item['expire_time'] else ''
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        columns = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'wx_name', 'label': '进群姓名'},
            {'prop': 'position', 'label': '职位'},
            {'prop': 'wx_code', 'label': '微信号'},
            {'prop': 'finish_time', 'label': '完成时间', 'width': 160},
            {'prop': 'expire_time', 'label': '有效期', 'width': 100},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return rows, columns

    def ruizhi_handler(self, rows):
        coordination_name = CoordinationType.get_name(CoordinationType.RUIZHI)
        coordination_key = CoordinationType.RUIZHI.value
        for item in rows:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['use_name'] + '瑞智试用'
            item['coordinate_time'] = None
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['expire_time'] = datetime_utils.to_string(item['expire_time'], is_time=False)
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        columns = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'use_name', 'label': '使用人'},
            {'prop': 'position', 'label': '职位'},
            {'prop': 'use_phone', 'label': '手机'},
            {'prop': 'expire_time', 'label': '完成时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return rows, columns


class CoordinationManageList(CoordinationRowHandler):
    def __init__(self, coordination: CoordinationType, state: int, page: int, page_size: int):
        self.coordination = coordination
        self.state = state
        self.page = page
        self.page_size = page_size

    def all(self, cursor):
        self.page = 1
        self.page_size = 9999
        ret = {'rows': [], 'coordination': [{'value': 'ALL', 'name': '全部', 'icon': 'volleyball-ball', 'handleCount': 0}]}
        for val in CoordinationType:
            exec_func = getattr(self, char_utils.hump2snake(val.value), None)
            if val == val.ALL or not exec_func:
                continue
            coo_data = exec_func(cursor=cursor)

            ret['rows'].extend(coo_data['data'])
            # noinspection PyTypeChecker
            ret['coordination'].append({
                'value': val.value,
                'name': CoordinationType.get_name(val),
                'icon': CoordinationType.get_icon(val),
                'handleCount': coo_data['total_count']
            })
        total_all = sum([item['handleCount'] for item in ret['coordination']])
        ret['coordination'][0]['handleCount'] = total_all
        ret['total_count'] = total_all
        ret['page'] = self.page
        ret['page_size'] = self.page_size
        ret['columns'] = [
            {'prop': 'coordinate_name', 'label': '类型', 'width': 160},
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '申请时间', 'width': 160},
            {'prop': 'coordinate_title', 'label': '名称'},
            {'prop': 'coordinate_time', 'label': '服务时间', 'width': 160},
        ]
        ret['rows'].sort(key=lambda x: x['create_time'], reverse=True)
        return ret

    def shou_quan(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_shouquan As a INNER JOIN ruizy_user As u ON a.creator_id=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.shou_quan_handler(ret['data'])
        ret['rows'] = rows
        ret['columns'] = columns
        return ret

    def zhen_duan(self, cursor):
        sql = """
           SELECT a.*,u.admin_name FROM prse_zhenduan As a INNER JOIN ruizy_user As u ON a.creator=u.id
       """
        if self.state == 0:  # 未完成
            sql += 'WHERE a.state>=0 AND a.state<3 ORDER BY a.create_time DESC;'
        elif self.state == -1:  # 已拒绝
            sql += 'WHERE a.state=-1 ORDER BY a.create_time DESC;'
        elif self.state == 1:  # 已完成
            sql += 'WHERE a.state=3 ORDER BY a.create_time DESC;'
        else:
            sql += ';'
        ret = cursor.self_conn.query_paginator(sql, [], page=self.page, page_size=self.page_size, keep_conn=True)

        coordination_name = CoordinationType.get_name(CoordinationType.ZHENDUAN)
        coordination_key = CoordinationType.ZHENDUAN.value
        for item in ret['data']:
            cur_flow = 5  # 已完成
            for flow in json.loads(item['flow_flag']):
                if flow['fs'] == 0:  # 当前流程所到之处
                    cur_flow = flow['state']
                    break
            item['cur_flow'] = cur_flow
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['use_name'] + '账户诊断'
            item['coordinate_time'] = None
            item['create_time'] = datetime_utils.to_string(item['create_time'], is_time=True)
            item['finish_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['expire_time'] = datetime_utils.to_string(item['expire_time'], is_time=False)
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            item['official_text'] = '是' if item['is_official'] else '否'
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']

        ret['columns'] = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'use_name', 'label': '账户姓名'},
            {'prop': 'use_account', 'label': '交易账户'},
            {'prop': 'use_phone', 'label': '客户手机'},
            {'prop': 'use_email', 'label': '客户邮箱', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
            {'prop': 'official_text', 'label': '正式购买'},
        ]
        return ret

    def ruizhi(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_ruizhi As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        coordination_name = CoordinationType.get_name(CoordinationType.RUIZHI)
        coordination_key = CoordinationType.RUIZHI.value
        for item in ret['data']:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['use_name'] + '瑞智试用'
            item['coordinate_time'] = None
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['expire_time'] = datetime_utils.to_string(item['expire_time'], is_time=False)
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        ret['columns'] = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'use_name', 'label': '使用人'},
            {'prop': 'position', 'label': '职位'},
            {'prop': 'use_phone', 'label': '手机'},
            {'prop': 'expire_time', 'label': '完成时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return ret

    # wind远程
    def wind_remote(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_wind_remote As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        coordination_name = CoordinationType.get_name(CoordinationType.WIND_REMOTE)
        coordination_key = CoordinationType.WIND_REMOTE.value
        for item in ret['data']:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['use_name'] + 'wind远程'
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = str(item['finish_time'])
            item['expire_time'] = datetime_utils.to_string(item['expire_time'], is_time=False)
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        ret['columns'] = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'use_name', 'label': '使用人'},
            {'prop': 'position', 'label': '职位'},
            {'prop': 'expire_time', 'label': '完成时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return ret

    # 微信沟通平台
    def wx_group(self, cursor):
        sql = """
           SELECT a.*,u.admin_name FROM prse_wx_group As a INNER JOIN ruizy_user As u ON a.creator=u.id
           WHERE a.state=%s ORDER BY a.create_time DESC;
       """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.wx_group_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret
    
    # 材料需求
    def data_need(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_data_need As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.data_need_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret

    # ppt课件
    def ppt_courseware(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_ppt As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        coordination_name = CoordinationType.get_name(CoordinationType.PPT_COURSEWARE)
        coordination_key = CoordinationType.PPT_COURSEWARE.value
        for item in ret['data']:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['title']
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = str(item['finish_time'])
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        ret['columns'] = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'title', 'label': '课件主题'},
            {'prop': 'company_name', 'label': '课件对象'},
            {'prop': 'duration', 'label': '课件时长'},
            {'prop': 'finish_time', 'label': '完成时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return ret

    # 分析报告
    def analysis_report(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_analysis_report As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.analysis_report_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret
        
    # 客户微信群服务
    def customer_online(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_customer_online As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.customer_online_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret

    # 套保方案
    def hedging(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_hedging_plan As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.hedging_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret
        
    # 投资计划
    def investment_plan(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_investment_plan As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.investment_plan_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret

    # 演讲培训
    def lecture(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_lecture As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.lecture_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret

    # 陪同走访
    def accompany(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_accompany_visit As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.accompany_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret

    def manage_list(self):
        with FAConnection() as cursor:
            execute_func = getattr(self, char_utils.hump2snake(self.coordination.value), None)
            if execute_func:
                ret = execute_func(cursor=cursor)
                return AllResponse.operate_successfully(data=ret)
        return AllResponse.no_do_anything(msg='该项目还不支持这个操作.')


class CoordinationWorkList(CoordinationRowHandler):
    def __init__(self, person, coordination: CoordinationType, state: int, page: int, page_size: int):
        self.person = person
        self.coordination = coordination
        self.state = state
        self.page = page
        self.page_size = page_size

    def all(self, cursor):
        self.page = 1
        self.page_size = 9999
        ret = {'rows': [], 'coordination': [{'value': 'ALL', 'name': '全部', 'icon': 'volleyball-ball', 'handleCount': 0}]}
        for val in CoordinationType:
            exec_func = getattr(self, char_utils.hump2snake(val.value), None)
            if val == val.ALL or not exec_func:
                continue
            coo_data = exec_func(cursor=cursor)

            ret['rows'].extend(coo_data['data'])
            # noinspection PyTypeChecker
            ret['coordination'].append({
                'value': val.value,
                'name': CoordinationType.get_name(val),
                'icon': CoordinationType.get_icon(val),
                'handleCount': coo_data['total_count']
            })
        total_all = sum([item['handleCount'] for item in ret['coordination']])
        ret['coordination'][0]['handleCount'] = total_all
        ret['total_count'] = total_all
        ret['page'] = self.page
        ret['page_size'] = self.page_size
        ret['columns'] = [
            {'prop': 'coordinate_name', 'label': '类型', 'width': 160},
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '申请时间', 'width': 160},
            {'prop': 'coordinate_title', 'label': '名称'},
            {'prop': 'coordinate_time', 'label': '服务时间', 'width': 160},
        ]
        ret['rows'].sort(key=lambda x: x['create_time'], reverse=True)
        return ret

    def lecture(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_lecture As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s AND a.worker_id=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state, self.person['uid']], page=self.page, page_size=self.page_size, keep_conn=True)
        coordination_name = CoordinationType.get_name(CoordinationType.LECTURE)
        coordination_key = CoordinationType.LECTURE.value
        for item in ret['data']:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['topic']
            item['coordinate_time'] = datetime_utils.to_string(item['lecture_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['lecture_time'] = str(item['lecture_time'])
            item['scale_plain'] = LECTURE_SCALE_TYPES.get(item['scale'], '未知')
            item['line_plain'] = LECTURE_TYPES.get(item['line'], '未知')
            item['line_scale'] = item['line_plain'][:2] + item['scale_plain'][2:]
            item['exchange_plain'] = EXCHANGE_LIB_NAME.get(item['exchange'], '无')
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']

        ret['columns'] = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'topic', 'label': '主题'},
            {'prop': 'target', 'label': '讲座对象'},
            {'prop': 'exchange_plain', 'label': '交易所', 'width': 100},
            {'prop': 'lecture_time', 'label': '讲课时间', 'width': 160},
            {'prop': 'line_scale', 'label': '类型规模', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return ret

    def accompany(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_accompany_visit As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s AND a.worker_id=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state, self.person['uid']], page=self.page, page_size=self.page_size, keep_conn=True)
        coordination_name = CoordinationType.get_name(CoordinationType.ACCOMPANY)
        coordination_key = CoordinationType.ACCOMPANY.value
        for item in ret['data']:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['visit_to']
            item['coordinate_time'] = datetime_utils.to_string(item['visit_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['visit_time'] = str(item['visit_time'])
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')

        ret['columns'] = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'visit_to', 'label': '走访对象'},
            {'prop': 'visit_site', 'label': '走访地点'},
            {'prop': 'visit_time', 'label': '走访时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return ret

    def investment_plan(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_investment_plan As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s AND a.worker_id=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state, self.person['uid']], page=self.page, page_size=self.page_size, keep_conn=True)
        coordination_name = CoordinationType.get_name(CoordinationType.INVESTMENT_PLAN)
        coordination_key = CoordinationType.INVESTMENT_PLAN.value

        for item in ret['data']:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['variety'] + '投资计划'
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = str(item['finish_time'])
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        ret['columns'] = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'variety', 'label': '投资品种'},
            {'prop': 'fund', 'label': '投资资金'},
            {'prop': 'cycle', 'label': '投资周期', 'width': 100},
            {'prop': 'finish_time', 'label': '完成时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return ret

    def hedging(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_hedging_plan As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s AND a.worker_id=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state, self.person['uid']], page=self.page, page_size=self.page_size, keep_conn=True)
        coordination_name = CoordinationType.get_name(CoordinationType.HEDGING)
        coordination_key = CoordinationType.HEDGING.value
        for item in ret['data']:
            item['coordinate_name'] = coordination_name
            item['coordinate_key'] = coordination_key
            item['coordinate_title'] = item['company_name'] + item['variety'] + '套保方案'
            item['coordinate_time'] = datetime_utils.to_string(item['finish_time'], is_time=True)
            item['create_time'] = str(item['create_time'])
            item['finish_time'] = str(item['finish_time'])
            item['state_text'] = COORDINATION_STATES.get(item['state'], '未知')
            if item['annex']:
                item['annex_url'] = STATIC_FILE_HOST + item['annex']
                del item['annex']
        ret['columns'] = [
            {'prop': 'admin_name', 'label': '申请人', 'width': 160},
            {'prop': 'create_time', 'label': '创建时间', 'width': 160},
            {'prop': 'company_name', 'label': '公司名称'},
            {'prop': 'company_type', 'label': '公司类型'},
            {'prop': 'variety', 'label': '涉及品种'},
            {'prop': 'cycle', 'label': '投资周期'},
            {'prop': 'hedge_scale', 'label': '套保规模'},
            {'prop': 'finish_time', 'label': '完成时间', 'width': 160},
            {'prop': 'link_msg', 'label': '联系方式'},
        ]
        return ret

    def customer_online(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_customer_online As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s AND a.worker_id=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state, self.person['uid']], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.customer_online_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret

    def analysis_report(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_analysis_report As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s AND a.worker_id=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state, self.person['uid']], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.analysis_report_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret

    def shou_quan(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_shouquan As a INNER JOIN ruizy_user As u ON a.creator_id=u.id
            WHERE a.state=%s AND a.worker_id=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state, self.person['uid']], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.shou_quan_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret

    def data_need(self, cursor):
        sql = """
            SELECT a.*,u.admin_name FROM prse_data_need As a INNER JOIN ruizy_user As u ON a.creator=u.id
            WHERE a.state=%s AND a.worker_id=%s ORDER BY a.create_time DESC;
        """
        ret = cursor.self_conn.query_paginator(sql, [self.state, self.person['uid']], page=self.page, page_size=self.page_size, keep_conn=True)
        rows, columns = self.data_need_handler(ret['data'])
        ret['data'] = rows
        ret['columns'] = columns
        return ret

    def work_list(self):
        with FAConnection() as cursor:
            execute_func = getattr(self, char_utils.hump2snake(self.coordination.value), None)
            if execute_func:
                ret = execute_func(cursor=cursor)
                return AllResponse.operate_successfully(data=ret)
        return AllResponse.no_do_anything(msg='该项目还不支持这个操作.')


class CoordinationDispatch:  # 分配任务，或者完成任务
    def __init__(self, coordination: CoordinationType):
        self.coordination = coordination
        self.coo_utils = CoordinationUtils(coordination)

    def lecture(self, person, dispatch_data: DispatchItem):
        now_time = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
        sql = "UPDATE prse_lecture SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s WHERE id=%s LIMIT 1;"
        prm = [dispatch_data.state, dispatch_data.explain_state, dispatch_data.worker_id, now_time, dispatch_data.record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_lecture WHERE id=%s LIMIT 1;', [dispatch_data.record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 执行更新
            if dispatch_data.state == -1:  # 当拒绝时应加回积分
                self.coo_utils.quota_change_log(user_id=person['uid'], prse_id=dispatch_data.record_id, cursor=cursor, change=-_obj['quota'])
        return AllResponse.operate_successfully(msg=f'处理【{CoordinationType.get_name(self.coordination)}】申请记录成功!')

    def accompany(self, person, dispatch_data: DispatchItem):
        now_time = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
        sql = "UPDATE prse_accompany_visit SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s WHERE id=%s LIMIT 1;"
        prm = [dispatch_data.state, dispatch_data.explain_state, dispatch_data.worker_id, now_time, dispatch_data.record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT id,quota FROM prse_accompany_visit WHERE id=%s LIMIT 1;', [dispatch_data.record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 执行分配
            if dispatch_data.state == -1:  # 拒绝记录
                self.coo_utils.quota_change_log(user_id=person['uid'], prse_id=dispatch_data.record_id, cursor=cursor, change=-_obj['quota'])
        return AllResponse.operate_successfully(msg=f'处理【{CoordinationType.get_name(self.coordination)}】申请记录成功!')
    
    def investment_plan(self, person, dispatch_data: DispatchItem):
        now_time = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
        sql = "UPDATE prse_investment_plan SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s WHERE id=%s LIMIT 1;"
        prm = [dispatch_data.state, dispatch_data.explain_state, dispatch_data.worker_id, now_time, dispatch_data.record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_investment_plan WHERE id=%s LIMIT 1;', [dispatch_data.record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 执行更新
            if dispatch_data.state == -1:  # 当拒绝时应加回积分
                self.coo_utils.quota_change_log(user_id=person['uid'], prse_id=dispatch_data.record_id, cursor=cursor, change=-_obj['quota'])
        return AllResponse.operate_successfully(msg=f'处理【{CoordinationType.get_name(self.coordination)}】申请记录成功!')
    
    def hedging(self, person, dispatch_data: DispatchItem):
        now_time = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
        sql = "UPDATE prse_hedging_plan SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s WHERE id=%s LIMIT 1;"
        prm = [dispatch_data.state, dispatch_data.explain_state, dispatch_data.worker_id, now_time, dispatch_data.record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_hedging_plan WHERE id=%s LIMIT 1;', [dispatch_data.record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 执行更新
            if dispatch_data.state == -1:  # 当拒绝时应加回积分
                self.coo_utils.quota_change_log(user_id=person['uid'], prse_id=dispatch_data.record_id, cursor=cursor, change=-_obj['quota'])
        return AllResponse.operate_successfully(msg=f'处理【{CoordinationType.get_name(self.coordination)}】申请记录成功!')

    def customer_online(self, person, dispatch_data: DispatchItem):
        now_time = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
        sql = "UPDATE prse_customer_online SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s WHERE id=%s LIMIT 1;"
        prm = [dispatch_data.state, dispatch_data.explain_state, dispatch_data.worker_id, now_time, dispatch_data.record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_customer_online WHERE id=%s LIMIT 1;', [dispatch_data.record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 执行更新
            if dispatch_data.state == -1:  # 当拒绝时应加回积分
                self.coo_utils.quota_change_log(user_id=person['uid'], prse_id=dispatch_data.record_id, cursor=cursor, change=-_obj['quota'])
        return AllResponse.operate_successfully(msg=f'处理【{CoordinationType.get_name(self.coordination)}】申请记录成功!')
        
    def analysis_report(self, person, dispatch_data: DispatchItem):
        now_time = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
        sql = "UPDATE prse_analysis_report SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s WHERE id=%s LIMIT 1;"
        prm = [dispatch_data.state, dispatch_data.explain_state, dispatch_data.worker_id, now_time, dispatch_data.record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_analysis_report WHERE id=%s LIMIT 1;', [dispatch_data.record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 执行更新
            if dispatch_data.state == -1:  # 当拒绝时应加回积分
                self.coo_utils.quota_change_log(user_id=person['uid'], prse_id=dispatch_data.record_id, cursor=cursor, change=-_obj['quota'])
        return AllResponse.operate_successfully(msg=f'处理【{CoordinationType.get_name(self.coordination)}】申请记录成功!')

    def ppt_courseware(self, person, dispatch_data: DispatchItem):
        now_time = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
        sql = "UPDATE prse_ppt SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s WHERE id=%s LIMIT 1;"
        prm = [dispatch_data.state, dispatch_data.explain_state, dispatch_data.worker_id, now_time, dispatch_data.record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_ppt WHERE id=%s LIMIT 1;', [dispatch_data.record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 执行更新
            if dispatch_data.state == -1:  # 当拒绝时应加回积分
                self.coo_utils.quota_change_log(user_id=person['uid'], prse_id=dispatch_data.record_id, cursor=cursor, change=-_obj['quota'])
        return AllResponse.operate_successfully(msg=f'处理【{CoordinationType.get_name(self.coordination)}】申请记录成功!')

    def data_need(self, person, dispatch_data: DispatchItem):
        now_time = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
        sql = "UPDATE prse_data_need SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s WHERE id=%s LIMIT 1;"
        prm = [dispatch_data.state, dispatch_data.explain_state, dispatch_data.worker_id, now_time, dispatch_data.record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_data_need WHERE id=%s LIMIT 1;', [dispatch_data.record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 执行更新
            if dispatch_data.state == -1:  # 当拒绝时应加回积分
                self.coo_utils.quota_change_log(user_id=person['uid'], prse_id=dispatch_data.record_id, cursor=cursor, change=-_obj['quota'])
        return AllResponse.operate_successfully(msg=f'处理【{CoordinationType.get_name(self.coordination)}】申请记录成功!')

    def dispatch(self, person: dict, dispatch_data: DispatchItem):
        execute_func = getattr(self, char_utils.hump2snake(self.coordination.value), None)
        if execute_func:
            return execute_func(person=person, dispatch_data=dispatch_data)
        return AllResponse.no_do_anything(msg='该项目不支持这个操作.')


class CoordinationFinish:
    def __init__(self, coordination: CoordinationType):
        self.coordination = coordination
        self.coor_utils = CoordinationUtils(coordination)

    # Worker
    async def lecture(self,  person: dict, record_id: int, state: int, state_desc: str, annex_public: bool, annex: UploadFile):
        if annex:
            # 写入文件
            save_path, sql_path = file_utils.get_file_paths(module_name='ProductService/WxGroup', filename=annex.filename, hashed=True)
            annex_content = await annex.read()
            with open(save_path, 'wb') as fp:
                fp.write(annex_content)
            annex_name = annex.filename
        else:
            save_path = ''
            sql_path = ''
            annex_name = ''

        # 更新数据
        now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        annex_public = 1 if annex_public else 0
        sql = "UPDATE prse_lecture SET annex_name=%s,annex_time=%s,annex=%s,annex_public=%s,state=2 WHERE id=%s LIMIT 1;"
        prm = [annex_name, now_time, sql_path, annex_public, record_id]
        with FAConnection() as cursor:
            cursor.execute(sql, prm)
        # 添加work_lecture记录
        # bg_tasks.add_task(add_work_lecture, rid)
        if save_path and state > 0:
            t = threading.Thread(target=self.coor_utils.covert_file2image, args=(pathlib.Path(save_path),))
            t.start()
        return AllResponse.operate_successfully()

    # Admin
    async def wx_group(self, person: dict, record_id: int, state: int, state_desc: str, annex_public: bool, annex: UploadFile):
        if annex:
            # 写入文件
            save_path, sql_path = file_utils.get_file_paths(module_name='ProductService/WxGroup', filename=annex.filename, hashed=True)
            annex_content = await annex.read()
            with open(save_path, 'wb') as fp:
                fp.write(annex_content)
            annex_name = annex.filename
        else:
            save_path = ''
            sql_path = ''
            annex_name = ''

        if state > 0:
            state = 2  # 直接显示完成

        now_time = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
        # 计算一年后的日期
        expire_time = (datetime.datetime.now() + datetime.timedelta(days=365)).strftime('%Y-%m-%d %H:%M:%S')
        sql = """
            UPDATE prse_wx_group SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s,finish_time=%s,expire_time=%s,
            annex_name=%s,annex_time=%s,annex=%s,annex_public=%s WHERE id=%s LIMIT 1;
        """
        prm = [state, state_desc, person['uid'], now_time, now_time, expire_time, annex_name, now_time, sql_path, annex_public, record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_wx_group WHERE id=%s LIMIT 1;', [record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 执行更新
            if state == -1:  # 当拒绝时应加回积分
                self.coor_utils.quota_change_log(user_id=person['uid'], prse_id=record_id, cursor=cursor, change=-_obj['quota'])
        if save_path and state > 0:
            t = threading.Thread(target=self.coor_utils.covert_file2image, args=(pathlib.Path(save_path),))
            t.start()
            # bg_task = BackgroundTasks()
            # bg_task.add_task(self.coor_utils.covert_file2image, pathlib.Path(save_path))
            # await bg_task()
        return AllResponse.operate_successfully(msg=f'完成【{CoordinationType.get_name(self.coordination)}】申请记录!')

    # Admin
    async def wind_remote(self, person: dict, record_id: int, state: int, state_desc: str, annex_public: bool, annex: UploadFile):
        if annex:
            # 写入文件
            save_path, sql_path = file_utils.get_file_paths(module_name='ProductService/WindRemote', filename=annex.filename, hashed=True)
            annex_content = await annex.read()
            with open(save_path, 'wb') as fp:
                fp.write(annex_content)
            annex_name = annex.filename
        else:
            save_path = ''
            sql_path = ''
            annex_name = ''
        # 计算一年后的日期
        expire_time = (datetime.datetime.now() + datetime.timedelta(days=365)).strftime('%Y-%m-%d %H:%M:%S')
        # 更新数据
        now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        if state == 1:
            state = 2
        sql = """
            UPDATE prse_wind_remote SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s,finish_time=%s,expire_time=%s, 
            annex_name=%s,annex_time=%s,annex=%s,annex_public=%s WHERE id=%s LIMIT 1;
        """

        prm = [state, state_desc, person['uid'], now_time, now_time, expire_time, annex_name, now_time, sql_path, annex_public, record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_wind_remote WHERE id=%s LIMIT 1;', [record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 更新数据
            if state == -1:  # 当拒绝时应加回积分
                self.coor_utils.quota_change_log(user_id=person['uid'], prse_id=record_id, cursor=cursor, change=-_obj['quota'])
        if save_path and state > 0:
            t = threading.Thread(target=self.coor_utils.covert_file2image, args=(pathlib.Path(save_path),))
            t.start()
        return AllResponse.operate_successfully(msg=f'完成【{CoordinationType.get_name(self.coordination)}】申请记录!')

    # Admin
    async def ruizhi(self, person: dict, record_id: int, state: int, state_desc: str, annex_public: bool, annex: UploadFile):
        if annex:
            # 写入文件
            save_path, sql_path = file_utils.get_file_paths(module_name='ProductService/Ruizhi', filename=annex.filename, hashed=True)
            annex_content = await annex.read()
            with open(save_path, 'wb') as fp:
                fp.write(annex_content)
            annex_name = annex.filename
        else:
            save_path = ''
            sql_path = ''
            annex_name = ''
        # 计算一年后的日期
        expire_time = (datetime.datetime.now() + datetime.timedelta(days=365)).strftime('%Y-%m-%d %H:%M:%S')
        # 更新数据
        now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        if state == 1:
            state = 2

        sql = """
            UPDATE prse_ruizhi SET state=%s,state_plain=%s,worker_id=%s,dispatch_time=%s,finish_time=%s,expire_time=%s,
            annex_name=%s,annex_time=%s,annex=%s,annex_public=%s WHERE id=%s LIMIT 1;
        """
        prm = [state, state_desc, person['uid'], now_time, now_time, expire_time, annex_name, now_time, sql_path, annex_public, record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_ruizhi WHERE id=%s LIMIT 1;', [record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 更新数据
            if state == -1:  # 当拒绝时应加回积分
                self.coor_utils.quota_change_log(user_id=person['uid'], prse_id=record_id, cursor=cursor, change=-_obj['quota'])
        if save_path and state > 0:
            t = threading.Thread(target=self.coor_utils.covert_file2image, args=(pathlib.Path(save_path),))
            t.start()
        return AllResponse.operate_successfully(msg=f'完成【{CoordinationType.get_name(self.coordination)}】申请记录!')

    async def shou_quan(self, person: dict, record_id: int, state: int, state_desc: str, annex_public: bool, annex: UploadFile):
        # 更新数据
        now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        if annex:
            # 写入文件
            save_path, sql_path = file_utils.get_file_paths(module_name='ProductService/Shouquan', filename=annex.filename, hashed=True)
            annex_content = await annex.read()
            with open(save_path, 'wb') as fp:
                fp.write(annex_content)
            annex_name = annex.filename
            annex_time = now_time
            print(save_path, sql_path)

        else:
            save_path = ''
            sql_path = ''
            annex_name = ''
            annex_time = None
        # 计算一年后的日期
        # expire_time = (datetime.datetime.now() + datetime.timedelta(days=365)).strftime('%Y-%m-%d %H:%M:%S')

        if state == 1:  # 为了统一state状态
            state = 2

        sql = """
            UPDATE prse_shouquan SET state=%s,state_plain=%s,worker_id=%s,finish_time=%s,
            annex_name=%s,annex_time=%s,annex_file=%s,annex_public=%s WHERE id=%s LIMIT 1;
        """
        prm = [state, state_desc, person['uid'], now_time, annex_name, annex_time, sql_path, annex_public, record_id]
        with FAConnection() as cursor:
            cursor.execute('SELECT * FROM prse_shouquan WHERE id=%s LIMIT 1;', [record_id])
            _obj = cursor.fetchone()
            if not _obj:
                return AllResponse.validate_error(msg='处理的记录不存在!')
            cursor.execute(sql, prm)  # 更新数据
            if state == -1:  # 当拒绝时应加回积分
                self.coor_utils.quota_change_log(user_id=person['uid'], prse_id=record_id, cursor=cursor, change=-_obj['quota'])
        if save_path and state > 0:
            t = threading.Thread(target=self.coor_utils.covert_file2image, args=(pathlib.Path(save_path),))
            t.start()
        return AllResponse.operate_successfully(msg=f'完成【{CoordinationType.get_name(self.coordination)}】申请记录!')

    # Admin
    async def finish(self, person: dict, record_id: int, state: int, state_desc: str, annex_public: bool, annex: UploadFile):
        execute_func = getattr(self, char_utils.hump2snake(self.coordination.value), None)
        if execute_func:
            return await execute_func(person=person, record_id=record_id, state=state, state_desc=state_desc,
                                      annex_public=annex_public, annex=annex)
        return AllResponse.no_do_anything(msg='该项目不支持这个操作.')


class CoordinationState(CoordinationRowHandler):
    def lecture(self, cursor, start, end):
        sql = """
        SELECT m.*,n.admin_name As work_name
        FROM (
            SELECT a.*,u.admin_name
            FROM prse_lecture As a INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.state>=0 AND a.create_time>=%s AND a.create_time<=%s)
        As m 
        INNER JOIN ruizy_user As n ON m.worker_id=n.id;
        """
        cursor.execute(sql, [start, end])
        rows = self.lecture_handler(list(cursor.fetchall()))[0]
        return self.statistics_handler(rows)

    def accompany(self, cursor, start, end):
        sql = """
        SELECT m.*,n.admin_name As work_name
        FROM
            (SELECT a.*,u.admin_name
            FROM prse_accompany_visit As a INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.state>=0 AND a.create_time>=%s AND a.create_time<=%s)
            As m
        INNER JOIN ruizy_user As n ON m.worker_id=n.id;
        """
        cursor.execute(sql, [start, end])
        rows = self.accompany_handler(list(cursor.fetchall()))[0]
        return self.statistics_handler(rows)

    def investment_plan(self, cursor, start, end):
        sql = """
        SELECT m.*,n.admin_name As work_name
        FROM(
            SELECT a.*,u.admin_name
            FROM prse_investment_plan As a INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.state>=0 AND a.create_time>=%s AND a.create_time<=%s)
            As m
        INNER JOIN ruizy_user As n ON m.worker_id=n.id;
        """
        cursor.execute(sql, [start, end])
        rows = self.investment_plan_handler(list(cursor.fetchall()))[0]
        return self.statistics_handler(rows)

    def hedging(self, cursor, start, end):
        sql = """
        SELECT m.*,n.admin_name As work_name
        FROM
            (SELECT a.*,u.admin_name
            FROM prse_hedging_plan As a INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.state>=0 AND a.create_time>=%s AND a.create_time<=%s)
            As m
        INNER JOIN ruizy_user As n ON m.worker_id=n.id;
        """
        cursor.execute(sql, [start, end])
        rows = self.hedging_handler(list(cursor.fetchall()))[0]
        return self.statistics_handler(rows)

    def customer_online(self, cursor, start, end):
        sql = """
        SELECT m.*,n.admin_name As work_name
        FROM (
            SELECT a.*,u.admin_name
            FROM prse_customer_online As a INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.state>=0 AND a.create_time>=%s AND a.create_time<=%s)
            As m
        INNER JOIN ruizy_user As n ON m.worker_id=n.id;
        """
        cursor.execute(sql, [start, end])
        rows = self.customer_online_handler(list(cursor.fetchall()))[0]
        return self.statistics_handler(rows)

    def wx_group(self, cursor, start, end):
        sql = """
        SELECT m.*,n.admin_name As work_name
        FROM (
            SELECT a.*,u.admin_name
            FROM prse_wx_group As a INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.state>=0 AND a.create_time>=%s AND a.create_time<=%s)
            As m
        INNER JOIN ruizy_user As n ON m.worker_id=n.id;
        """
        cursor.execute(sql, [start, end])
        rows = self.wx_group_handler(list(cursor.fetchall()))[0]
        return self.statistics_handler(rows)

    def ruizhi(self, cursor, start, end):
        sql = """
        SELECT m.*,n.admin_name As work_name
        FROM (
            SELECT a.*,u.admin_name
            FROM prse_ruizhi As a INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.state>=0 AND a.create_time>=%s AND a.create_time<=%s)
            As m
        INNER JOIN ruizy_user As n ON m.worker_id=n.id;
        """
        cursor.execute(sql, [start, end])
        rows = self.ruizhi_handler(list(cursor.fetchall()))[0]
        return self.statistics_handler(rows)

    def data_need(self, cursor, start, end):
        sql = """
        SELECT m.*,n.admin_name As work_name
        FROM (
            SELECT a.*,u.admin_name
            FROM prse_data_need As a INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.state>=0 AND a.create_time>=%s AND a.create_time<=%s)
            As m
        INNER JOIN ruizy_user As n ON m.worker_id=n.id;
        """
        cursor.execute(sql, [start, end])
        rows = self.data_need_handler(list(cursor.fetchall()))[0]
        return self.statistics_handler(rows)

    def statistics_handler(self, rows):
        # 执行统计，申请数据，完成数量，进行的数量
        # { coordination_key: , coordination_name: , total_count: , finish_count: , doing_count: }
        stat = {
            'coordinate_key': None, 'coordinate_name': None,
            'total_count': 0, 'finish_count': 0, 'doing_count': 0,
            'total_quota': 0, 'finish_quota': 0, 'doing_quota': 0,
            'details': [],
        }

        for item in rows:
            stat['total_count'] += 1
            stat['total_quota'] += item['quota']
            stat['details'].append(item)
            if not stat['coordinate_key'] or not stat['coordinate_name']:
                stat['coordinate_key'] = item['coordinate_key']
                stat['coordinate_name'] = item['coordinate_name']
            if item['state'] == 2:
                stat['finish_count'] += 1
                stat['finish_quota'] += item['quota']
            else:
                stat['doing_count'] += 1
                stat['doing_quota'] += item['quota']
        for k, v in stat.items():
            if isinstance(v, int) and v == 0:
                stat[k] = ''

        return stat

    def statistics(self, person, start: str, end: str):
        start = start + ' 00:00:00'
        end = end + ' 23:59:59'
        rows_state = []
        with FAConnection() as cursor:
            for val in CoordinationType:
                exec_func = getattr(self, char_utils.hump2snake(val.value), None)
                if val == val.ALL or not exec_func:
                    continue
                coo_data = exec_func(cursor=cursor, start=start, end=end)
                if coo_data.get('coordinate_key'):
                    rows_state.append(coo_data)
        return AllResponse.operate_successfully(data=rows_state)


coordination_api = APIRouter()


@coordination_api.get('/manageList/')
def coordination_manage_list(coordination: CoordinationType = Query(...), state: int = Query(...),
                             page: int = Query(1), page_size: int = Query(10)):
    return CoordinationManageList(coordination=coordination, state=state, page=page, page_size=page_size).manage_list()


@coordination_api.post('/dispatch/')
def coordination_dispatch(ruizy_token: str = Header(...), coordination: CoordinationType = Query(...), dispatch_data: DispatchItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_manager(person):
        return AllResponse.forbidden_error()
    return CoordinationDispatch(coordination=coordination).dispatch(person=person, dispatch_data=dispatch_data)


@coordination_api.post('/finish/')
async def coordination_finish(ruizy_token: str = Header(...), coordination: CoordinationType = Query(...),
                              record_id: int = Form(...), state: int = Form(2), state_desc: str = Form(''),
                              annex_public: bool = Form(False), annex: UploadFile = Form(None)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    return await CoordinationFinish(coordination=coordination).finish(person=person, record_id=record_id, state=state, state_desc=state_desc,
                                                                      annex_public=annex_public, annex=annex)


@coordination_api.get('/workList/')
def coordination_work_list(ruizy_token: str = Header(...),
                           coordination: CoordinationType = Query(...), state: int = Query(...),
                           page: int = Query(1), page_size: int = Query(10)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    return CoordinationWorkList(person=person, coordination=coordination, state=state, page=page, page_size=page_size).work_list()


@coordination_api.get('/statistics/')
def coordination_work_statistics(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(start, f='%Y-%m-%d')
    end_date = datetime_utils.auth_datetime_string(end, f='%Y-%m-%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='参数错误!')
    return CoordinationState().statistics(person=person, start=start_date, end=end_date)

